package presenters;

import java.sql.ResultSet;

import model.ConnectionManager;
import model.IPresenter;
import views.LocationPrefReportPanel;

public class LocationPrefReportPresenter extends IPresenter {
	private LocationPrefReportPanel myPanel;
	
	public LocationPrefReportPresenter(LocationPrefReportPanel panel) {
		super();
		myPanel = panel;
	}
	
	
	public void generateReport() {
		System.out.println("Generate Location Preference Report");
		
		String sqlD = ""
			+ "DROP VIEW " + ConnectionManager.GROUP + ".RES_THREE_MO;"
			+ "DROP VIEW " + ConnectionManager.GROUP + ".RES_SUMMARY;";
		boolean out1 = executeSQLCreate(sqlD);
		
		String sql3Month = ""	
			+ "CREATE VIEW  " + ConnectionManager.GROUP + ".RES_THREE_MO AS "
			+ "SELECT "
				+ "RES_ID, "
				+ "PKUP_DTTM, "
				+ "RNT_LOC, "
				+ "TIMESTAMPDIFF(SECOND,PKUP_DTTM,RETN_DTTM)/3600 AS HOURS_RNTD "
			+ "FROM " + ConnectionManager.GROUP + ".RESERVATION "
			+ "WHERE DATE(PKUP_DTTM) >= SUBDATE(CURDATE(), INTERVAL 3 MONTH); ";
			
		String sqlCreateSummary = ""
			+ "CREATE VIEW RES_SUMMARY AS "
			+ "SELECT "
				+ "MONTH(PKUP_DTTM) AS MONTH, "
				+ "RNT_LOC, "
				+ "COUNT(*) AS NMBR_RES, "
				+ "SUM(HOURS_RNTD) AS HRS "
			+ "FROM " + ConnectionManager.GROUP + ".RES_THREE_MO "
			+ "GROUP BY MONTH(PKUP_DTTM), RNT_LOC; ";
	
		String sqlGet = ""
			+ "SELECT DISTINCT "
				+ "MONTH AS Month, "
				+ "RNT_LOC AS Location, "
				+ "NMBR_RES AS `No of Reservations`, "
				+ "HRS AS `Total no of Hours` "
			+ "FROM " + ConnectionManager.GROUP + ".RES_SUMMARY "
			+ "WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 1 MONTH)) AND NMBR_RES = (SELECT MAX(NMBR_RES) FROM " + ConnectionManager.GROUP + ".RES_SUMMARY WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 1 MONTH))) "
			+ "UNION "
			+ "SELECT DISTINCT "
				+ "MONTH AS Month, "
				+ "RNT_LOC AS Location, "
				+ "NMBR_RES AS `No of Reservations`, "
				+ "HRS AS `Total no of Hours` "
			+ "FROM " + ConnectionManager.GROUP + ".RES_SUMMARY "
			+ "WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 2 MONTH)) AND NMBR_RES = (SELECT MAX(NMBR_RES) FROM " + ConnectionManager.GROUP + ".RES_SUMMARY WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 2 MONTH))) "
			+ "UNION "
			+ "SELECT DISTINCT "
				+ "MONTH AS Month, "
				+ "RNT_LOC AS Location, "
				+ "NMBR_RES AS `No of Reservations`, "
				+ "HRS AS `Total no of Hours` "
			+ "FROM " + ConnectionManager.GROUP + ".RES_SUMMARY "
			+ "WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 3 MONTH)) AND NMBR_RES = (SELECT MAX(NMBR_RES) FROM " + ConnectionManager.GROUP + ".RES_SUMMARY WHERE MONTH = MONTH(SUBDATE(CURDATE(), INTERVAL 3 MONTH)));";     

		
		
		boolean out2 = executeSQLCreate(sql3Month);
		boolean out3 =  executeSQLCreate(sqlCreateSummary);
		ResultSet rs = executeSQLQuery(sqlGet);
		
		System.out.println("" + out1 + "\t" + out2+ "\t" + out3);
		myPanel.addData(rs);

	}

}
